/*
 * @Description:
 * @Author: Dxcr
 * @Date: 2024-08-20 09:52:29
 * @LastEditTime: 2024-08-20 17:47:36
 * @LastEditors: Dxcr
 */
use calamine::{open_workbook_auto, Reader, Sheets};
use serde_json::{Map, Number, Value};
use std::fs::File;
use std::io::Write;

pub struct ExcelReader {
    sheets: Sheets, // 使用 Sheets 处理不同格式的工作簿
}

impl ExcelReader {
    pub fn new(file_path: &str) -> Result<Self, Box<dyn std::error::Error>> {
        // 使用 open_workbook_auto 自动处理不同格式的 Excel 文件
        let sheets = open_workbook_auto(file_path)?;
        Ok(Self { sheets })
    }

    pub fn read_sheet_as_json(
        &mut self,
        sheet_name: &str,
    ) -> Result<String, Box<dyn std::error::Error>> {
        let range = self
            .sheets
            .worksheet_range(sheet_name)
            .ok_or("Cannot find the specified sheet")??;

        let mut rows = Vec::new();

        let headers: Vec<String> = range
            .rows()
            .next()
            .ok_or("The sheet is empty")?
            .iter()
            .map(|cell| cell.to_string())
            .collect();

        for row in range.rows().skip(1) {
            let mut map = Map::new();
            for (header, cell) in headers.iter().zip(row.iter()) {
                let cell_string = cell.to_string();
                
                if let Ok(value) = cell_string.parse::<i64>() {
                    map.insert(header.clone(), Value::Number(Number::from(value)));
                }
                else if let Ok(value) = cell_string.parse::<f64>() {
                    map.insert(header.clone(), Value::Number(Number::from_f64(value).unwrap()));
                }
                else {
                    map.insert(header.clone(), Value::String(cell_string));
                }
            }
            rows.push(Value::Object(map));
        }
        let json = serde_json::to_string_pretty(&rows)?;
        Ok(json)
    }

    pub fn save_json_to_file(
        &mut self,
        sheet_name: &str,
        output_file: &str,
    ) -> Result<(), Box<dyn std::error::Error>> {
        let json = self.read_sheet_as_json(sheet_name)?;
        let mut file = File::create(output_file)?;
        file.write_all(json.as_bytes())?;
        Ok(())
    }
}
